package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import javax.annotation.Resource;

import org.springframework.orm.ibatis.SqlMapClientTemplate;
import org.springframework.stereotype.Repository;

@Repository("QueryDAO")
public class QueryDAO {
	
	@Resource(name="sqlMapClientTemplate")
	private SqlMapClientTemplate sqlmap;
	
	public HashMap selectQueryByTxt(String sql) throws SQLException {
		ArrayList list= new ArrayList();
		ArrayList headList = new ArrayList();
		HashMap<String, ArrayList> map = new HashMap<String, ArrayList>();
		
		Connection con = null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		ResultSetMetaData metaResult = null;		//metaData 
		
		try{
			con = sqlmap.getDataSource().getConnection();
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			metaResult = rs.getMetaData();
			
			int cols = metaResult.getColumnCount();		//칼럼수 
			for(int i=1; i<=cols; i++){
				headList.add(metaResult.getColumnName(i));		//칼럼 이름을 collection에 넣기
			}
			
			while(rs.next()){			//쿼리 결과값들
				ArrayList objArr = new ArrayList();
				for(int j=1; j<=cols; j++){
					Object tempObj = rs.getObject(j) ;
					if(tempObj instanceof Integer){
						objArr.add(rs.getInt(j));
					} else if(tempObj instanceof String){
						objArr.add(rs.getString(j));
					} else if(tempObj instanceof Date){
						objArr.add(rs.getDate(j));
					} else {
						objArr.add(rs.getObject(j));
					}
				}
				list.add(objArr);
			}
			map.put("list", list);
			map.put("headList", headList);
			
		} finally{
			if(rs!=null){
				rs.close();
			}
			if(pre!=null){
				pre.close();
			}
			if(con!=null){
				con.close();
			}
		}
		return map;
	}
	
	
	
}
